package com.dsi.capa.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import com.dsi.capa.db.model.Pago;
import com.dsi.capa.db.model.Talonario;

public class TalonarioDAO extends BasicDAO<Talonario>{
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public TalonarioDAO() {
		super();
	}

	public TalonarioDAO(DataSource ds) {
		super(ds);
	}

	// insertar en la base

	public void savePago(Talonario target) {

		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO PAGO(IDCOLECTOR, MONTOPAGO, FECHAPAGO, CARNET,DESCRIPCIONPAGO,CUOTA)");
		sql.append(" VALUES( ?, ?, STR_TO_DATE(?,'%d-%m-%Y'), ?, ?,?)");
		try {
			preparedStatement = getConnection()
					.prepareStatement(sql.toString());
			preparedStatement.setString(1, "col001");
			preparedStatement.setDouble(2, target.getMonto());
			
			preparedStatement.setString(3, target.getFecha());
			preparedStatement.setString(4, target.getCarnet());
			preparedStatement.setString(5, target.getDescripcion());
			preparedStatement.setString(6, target.getCuota().toString());
			
//			preparedStatement.setString(1, "11");
//			preparedStatement.setString(2, "1");
//			preparedStatement.setInt(3, 9);
//			preparedStatement.setDouble(4,40);
//			preparedStatement.setString(5,"2013-04-04");
//			preparedStatement.setString(6, "ac05123");
			 
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
	}

	@Override
	public void update(Talonario target) {
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE FACTURA SET ESTADOPAGO=?");
		//sql.append("WHERE CARNET = ? AND FECHA = ?");
		sql.append(" WHERE CARNET = ? AND CUOTA=?");
		try {
			preparedStatement = getConnection()
					.prepareStatement(sql.toString());

//			preparedStatement.setString(1, target.getCarnet());
//			preparedStatement.setString(2, target.getFecha());
//			preparedStatement.setDouble(3, target.getMonto());
			preparedStatement.setString(1, "Cancelado");
			preparedStatement.setString(2, target.getCarnet());
			preparedStatement.setInt(3, target.getCuota());
			//preparedStatement.setDate(5, target.getFecha());
			
			preparedStatement.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		
		savePago(target);
	}

	@Override
	public void delete(Talonario target) {
		StringBuffer sql = new StringBuffer();
		sql.append("DELETE FROM Factura WHERE CARNET = ? ");
		try {
			preparedStatement = getConnection()
					.prepareStatement(sql.toString());
			preparedStatement.setString(1, target.getCarnet());
			// preparedStatement.setLong(1, target.getCodigo());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
	}

	@Override
	public List<Talonario> findAll() {
		return findAll(null, null);
	}

	/************************* METODO QUE BUSCA UN SOLO PAGO *********************************/
	/*
	 * public Talonario buscarTalonario(Talonario target) { Talonario c = new Talonario(); StringBuffer
	 * sql = new StringBuffer();
	 * sql.append("SELECT carnet, fecha, idusuario, ");
	 * sql.append("descripcion, cuota, monto FROM Factura WHERE CARNET = ?");
	 * 
	 * try { preparedStatement =
	 * getConnection().prepareStatement(sql.toString());
	 * preparedStatement.setString(1, target.getCarnet()); resultSet =
	 * preparedStatement.executeQuery();
	 * 
	 * while(resultSet.next()) { c.setCarnet(resultSet.getString("CARNET"));
	 * c.setFecha(resultSet.getString("fecha"));
	 * c.setIdUsuario(resultSet.getString("idusuario"));
	 * c.setDescripcion(resultSet.getString("descripcion"));
	 * c.setCuota(resultSet.getInt("cuota"));
	 * c.setMonto(resultSet.getDouble("monto"));
	 * 
	 * }
	 * 
	 * 
	 * } catch (SQLException e) { e.printStackTrace(); } finally {
	 * closeConnection(); }
	 * 
	 * return c;
	 * 
	 * }//FIN METODO buscarTalonario
	 */

//	public String obtenerMontoCuota(Talonario target) {
//
//		String result = null;
//
//		StringBuffer sql = new StringBuffer();
//		sql.append("SELECT * from carrera where =?");
//
//		try {
//			preparedStatement = getConnection()
//					.prepareStatement(sql.toString());
//			preparedStatement.setString(1, target.getDropdown());
//			resultSet = preparedStatement.executeQuery();
//
//			while (resultSet.next()) {
//				result = resultSet.getString("MONTOCUOTA");
//
//			}
//
//		} catch (SQLException e) {
//			e.printStackTrace();
//		} finally {
//			closeConnection();
//		}
//
//		return result;
//	}

	/************************* METODO QUE BUSCA UN SOLO PAGO *********************************/
	/*
	 * public String correlativo(Talonario target) { //Talonario c = new Talonario(); String
	 * c=null; StringBuffer sql = new StringBuffer();
	 * sql.append("SELECT CARNET FROM Talonario WHERE CARNET LIKE ?");
	 * 
	 * try { preparedStatement =
	 * getConnection().prepareStatement(sql.toString());
	 * preparedStatement.setString(1, target.getCarnet() + "%"); resultSet =
	 * preparedStatement.executeQuery();
	 * 
	 * if(resultSet.last()) { c = resultSet.getString("CARNET"); c =
	 * c.substring(4);//OBTENER ULTIMO CORRELATIVO //INCREMENTAR CORRELATIVO c =
	 * String.format("%03d", (Integer.parseInt(c))+1); } //return c;
	 * 
	 * } catch (SQLException e) { e.printStackTrace(); } finally {
	 * closeConnection(); }
	 * 
	 * return c;
	 * 
	 * }//FIN METODO buscarTalonario
	 */

	/** Bisquedas y navegacion **/

	public List<Talonario> findBy(String carnet,Integer firstRow,Integer maxRows) {
		List<Talonario> registros = new ArrayList<Talonario>();
		StringBuffer sql = new StringBuffer();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int i = 1;
		int pos = firstRow.intValue() + maxRows.intValue();
		sql.append("SELECT CARNET ");
		sql.append("     , DATE_FORMAT(FECHA,'%d-%m-%Y') AS FECHA");
		sql.append("     , DESCRIPCION ");
		sql.append("     , CUOTA ");
		sql.append("     , MONTO ");
		sql.append("     , TIPOPAGO ");
		sql.append("     , ESTADOPAGO ");
		sql.append("     , MORA ");	
		sql.append(" FROM FACTURA ");
		sql.append(" WHERE 1 = 1 ");
	
		if (!isEmpty(carnet)) {
			sql.append(" AND CARNET LIKE ?");
		}
		

		try {
			connection = ds.getConnection();
			preparedStatement = connection.prepareStatement(sql.toString());
			if (!isEmpty(carnet)) {
				preparedStatement.setString(i++, carnet);
			}
			resultSet = preparedStatement.executeQuery();
			i = 0;
			while (resultSet.next()) {
				if (i >= firstRow) {
					Talonario cn = getDataRow(resultSet);
					registros.add(cn);
					if (i >= pos) {
						break;
					}
				}
				i++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return registros;
	}

	private Talonario getDataRow(ResultSet resultSet) throws SQLException {
		Talonario cn = new Talonario();
		cn.setCarnet(resultSet.getString("CARNET"));
		cn.setFecha(resultSet.getString("FECHA"));
		cn.setDescripcion(resultSet.getString("DESCRIPCION"));
		cn.setCuota(resultSet.getInt("CUOTA"));
		cn.setMonto(resultSet.getDouble("MONTO"));
		cn.setTipoPago(resultSet.getString("TIPOPAGO"));
		cn.setEstadoPago(resultSet.getString("ESTADOPAGO"));
		cn.setMora(resultSet.getDouble("MORA"));
		//cn.setCuota(resultSet.getDouble("CUOTA"));

		return cn;
	}

	private boolean isEmpty(Long target) {
		return target == null || target.longValue() == 0;
	}

	private boolean isEmpty(String target) {
		return target == null || target.trim().equals("");
	}

	private boolean isEmpty(Integer target) {
		return target == null || target.intValue() == 0;
	}

	private boolean isEmpty(Date target) {
		return target == null || target.toString().equals("");
	}

	@Override
	public List<Talonario> findAll(Integer firstRow, Integer maxRows) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public void save(Talonario target) {
		// TODO Auto-generated method stub
		
	}

	/**** ----------------------------------- ***/
}
